package devbureau.fstore.util;

import devbureau.fstore.common.NoConnectionException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import org.apache.log4j.Logger;

public class DBHelper {

	private static final Logger log = Logger.getLogger(DBHelper.class);
	
	private static final String PROP_USE_JNDI = "db.use_jndi";
	private static final String PROP_JNDI_NAME = "db.jndi_name";
	private static final String PROP_USER = "db.user";
	private static final String PROP_PASSWORD = "db.password";
	private static final String PROP_DRIVER = "db.driver";
	private static final String PROP_URL = "db.url";
	
	private static final String DB_SCHEME = "store.";

	private static String jdbcJndiName = null;
	private static DataSource dataSource = null;
	
	private static synchronized void checkDS() throws Exception {
		
		if ("false".equals(PropertyHelper.getProperty(PROP_USE_JNDI))) {
			try {
				Class.forName(PropertyHelper.getProperty(PROP_DRIVER));
				log.debug("Class.forName done");
			} catch (Exception e) {
				log.error("#checkDS()", e);
			}
		} else {
			if (jdbcJndiName == null) {
				jdbcJndiName = PropertyHelper.getProperty(PROP_JNDI_NAME);
			}
			if (dataSource == null) {
				try {
					InitialContext ict = new InitialContext();
                    Context ect  = (Context)ict.lookup("java:/comp/env");
					dataSource = (DataSource) ect.lookup(jdbcJndiName);
				} catch (Exception e) {
					log.error("#checkDS() ", e);
				}
			}
		}
	}

	/**
	 * @return connection not use JNDI
	 */
	public static Connection getConnectionDirectly() {
		log.info("#getConnectionDirectly() enter");
		Connection con = null;
		try {
			log.debug("urs = " + PropertyHelper.getProperty(PROP_URL));
			log.debug("user = " + PropertyHelper.getProperty(PROP_USER));
			log.debug("password = " + PropertyHelper.getProperty(PROP_PASSWORD));
			Class.forName(PropertyHelper.getProperty(PROP_DRIVER));
			con = java.sql.DriverManager.getConnection(PropertyHelper.getProperty(PROP_URL), 
					PropertyHelper.getProperty(PROP_USER), PropertyHelper.getProperty(PROP_PASSWORD));
		} catch (Exception e) {
			log.error("#getConnectionDirectly()", e);
		}
		log.info("#getConnectionDirectly() exit");
		return con;
	}

	public static Connection getConnection() {
		log.info("#getConnection() enter");
		Connection con = null;
		try {
			checkDS();
			if (PropertyHelper.getProperty(PROP_USE_JNDI).equals("false")) {
				log.debug("urs = " + PropertyHelper.getProperty(PROP_URL));
				log.debug("user = " + PropertyHelper.getProperty(PROP_USER));
				log.debug("password = " + PropertyHelper.getProperty(PROP_PASSWORD));
				Class.forName(PropertyHelper.getProperty(PROP_DRIVER));
				con = java.sql.DriverManager.getConnection(PropertyHelper
						.getProperty(PROP_URL), PropertyHelper
						.getProperty(PROP_USER), PropertyHelper
						.getProperty(PROP_PASSWORD));
			} else {
				con = dataSource.getConnection();
			}
		} catch (Exception e) {
			log.error("#getConnection()", e);
            throw new NoConnectionException("Ошибка при соединении с базой данных. Проверьте, включена ли база данных.");
		}
		log.info("#getConnection() exit");
		return con;
	}

	public static void closeConnection(Connection con) {
		try {
			if (con != null) {
				con.close();
			}
		} catch (Exception e) {
			log.error("Exception!!! " + e.getMessage(), e);
		}
	}

	public static void closeStatement(Statement st) {
		try {
			if (st != null) {
				st.close();
			}
		} catch (Exception e) {
			log.error("Exception!!! " + e.getMessage(), e);
		}
	}

	public static void closeResultSet(ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (Exception e) {
			log.error("Exception!!! " + e.getMessage(), e);
		}
	}

    public static void closeAll(Connection con, ResultSet rs, PreparedStatement ps){
            closeResultSet(rs);
            closeStatement(ps);
            closeConnection(con);
    }

	public static Long getSequenceNextValue(Connection outerCon, String seqName)
			throws SQLException {
		String SQL = "values nextval for " + DB_SCHEME + seqName;
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		Long res = null;
		try {
			
			if (outerCon == null) {
				conn = getConnection();
			} else {
				conn = outerCon;
			}
			
			stmt = conn.createStatement();
			rs = stmt.executeQuery(SQL);
			if (rs.next()) {
				res = new Long(rs.getLong(1));
			}
		} finally {
			closeResultSet(rs);
			closeStatement(stmt);
			if (outerCon == null) {
				closeConnection(conn);
			}
		}
		return res;
	}
	
	public static void executeUpdate(String sql) {
		log.info("#executeUpdate() enter");
		log.debug("#executeUpdate() sql = [" + sql + "]");
		Connection con = null;
		Statement st = null;
		try {
			con = DBHelper.getConnection();
			st = con.createStatement();
			st.executeUpdate(sql);
		} catch (Exception e) {
			log.error("#executeUpdate()" + e, e);
		} finally {
			DBHelper.closeStatement(st);
			DBHelper.closeConnection(con);
		}
		log.info("#executeUpdate() exit");
	}

	public static void rollback(Connection con) {
		try {
			if (con != null) {
				con.rollback();
			}
		} catch (Exception e) {
			log.error("#rollback()" + e, e);
		}
	}

    public static java.sql.Date toSQL(java.util.Date javaDate){
        return new java.sql.Date(javaDate.getTime());
    }

	public static void setStringValue(PreparedStatement st, int index, String value) throws SQLException {
		if (value == null) {
			st.setNull(index, Types.VARCHAR);
		} else {
			st.setString(index, value);
		}
	}
	
}
